package com.asdan.seededu.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.asdan.seededu.entity.security.SubProject;
import com.asdan.seededu.entity.security.SubProjectStep;
import com.asdan.seededu.entity.security.User;
import com.asdan.seededu.entity.security.Userdata;
import com.asdan.seededu.service.ProjectService;
import com.asdan.seededu.util.DatabaseAccess;
import com.asdan.seededu.util.Pager;
import com.asdan.seededu.util.Util;
import com.demo2do.core.utils.JsonUtils;


public class ProjectServiceImpl extends UtilImpl implements ProjectService {

	public List<SubProject> getProjects(String fpid,String spid) {
		// TODO Auto-generated method stub
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs = null;
		List<SubProject> spList = new ArrayList<SubProject>()  ;
		String hql = "" ;
		if(fpid !=null&&!"".equals(fpid)){
			hql += " AND fpid = "+ fpid;
		}else{
			hql += " AND spid = "+ spid;	
		}
		
		String sql = "select spid , fpid , name , pcreatTime ,startTime ,endTime from subproject  where  1=1 ";
		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql + hql);
			System.out.println(sql + hql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				
				SubProject sp= new SubProject();
				
  				sp.setSpid(rs.getInt("spid")) ;
  				sp.setFpid(rs.getInt("fpid")) ;
  				sp.setName(rs.getString("name")) ;
  				sp.setPcreatTime(rs.getInt("pcreatTime")) ;
  				sp.setStartTime(rs.getInt("startTime")) ;
  				sp.setEndTime(rs.getInt("endTime")) ;
  				//sp.setTuid(rs.getInt("tuid")) ;
				
				spList.add(sp) ;
				
			}
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
			}
		}
		return spList;
	}

	public boolean addProjects(String fpid, String spid) {
		// TODO Auto-generated method stub
		
		return false;
	}

	public List<SubProjectStep> getProjectStep(int spid) {
		// TODO Auto-generated method stub
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs = null;
		List<SubProjectStep> spsList = new ArrayList<SubProjectStep>()  ;
		String sql = "select stepid , spid , name , ifpay ,stepname,stepcontext,stepillustrate,attachment1name,attachment1address,attachment2name,attachment2address" +
				",attachment3name,attachment3address,attachment4name,attachment4address ,attachment5name,attachment5address from SubProjectStep  where spid = ? ";
		System.out.println(sql);
		try{
			
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setInt(1,spid ) ;
			rs = pstmt.executeQuery();
			while(rs.next()){
				
				SubProjectStep sps= new SubProjectStep();
				sps.setStepid(rs.getInt("stepid"))  ;
  				sps.setSpid(rs.getInt("spid")) ;
  				sps.setName(rs.getString("name")) ;
  				sps.setIfpay(rs.getInt("ifpay")) ;
  				sps.setStepName(rs.getString("stepname")) ;
  				sps.setStepContext(rs.getString("stepcontext")) ;
  				sps.setStepillustrate(rs.getString("stepillustrate")) ;
  				System.out.println();
  				sps.setAttachment1name(rs.getString("attachment1name")) ;
  				sps.setAttachment1address(rs.getString("attachment1address")) ;
  				sps.setAttachment2name(rs.getString("attachment2name")) ;
  				sps.setAttachment2address(rs.getString("attachment2address")) ;
  				sps.setAttachment3name(rs.getString("attachment3name")) ;
  				sps.setAttachment3address(rs.getString("attachment3address")) ;
  				sps.setAttachment4name(rs.getString("attachment4name")) ;
  				sps.setAttachment4address(rs.getString("attachment4address")) ;
  				sps.setAttachment5name(rs.getString("attachment5name")) ;
  				sps.setAttachment5address(rs.getString("attachment5address")) ;
  				spsList.add(sps) ;
			}
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
			}
		}
		return spsList;
	}

	public List<SubProject> searchPro(String name) {
		// TODO Auto-generated method stub
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs = null;
		List<SubProject> spList = new ArrayList<SubProject>()  ;
		
		String sql = "select spid , fpid , name , pcreatTime ,startTime ,endTime,tuid from subproject  where  name LIKE '%"+name+"%' ";
		System.out.println(sql) ;
		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql );
			rs = pstmt.executeQuery();
			while(rs.next()){
				
				SubProject sp= new SubProject();
  				sp.setSpid(rs.getInt("spid")) ;
  				sp.setFpid(rs.getInt("fpid")) ;
  				sp.setName(rs.getString("name")) ;
  				sp.setPcreatTime(rs.getInt("pcreatTime")) ;
  				sp.setStartTime(rs.getInt("startTime")) ;
  				sp.setEndTime(rs.getInt("endTime")) ;
  				sp.setTuid(rs.getInt("tuid")) ;
				
				spList.add(sp) ;
				
			}
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
			}
		}
		return spList;
	}

	public boolean updateSubProjectStep(SubProjectStep sps) {
		
		boolean temp = true ;
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		String sql = "update SubProjectStep  set  stepName=? ,stepillustrate=? ,stepContext=? ,attachment1name=?,attachment1address=?,attachment2name=?,attachment2address=?," +
				"attachment3name=?,attachment3address=?,attachment4name=?,attachment4address=?,attachment5name=?,attachment5address=? where stepid = ?" ;
		System.out.println(sql) ;
		System.out.println(sps.getStepid()) ;
		System.out.println(sps.getStepName());
		System.out.println(sps.getStepillustrate());
		System.out.println(sps.getStepContext());
		System.out.println(sps.getAttachment1name());
		System.out.println(sps.getAttachment1address());
		try {
			cn = databaseAccess.getConn() ;
			pstmt = cn.prepareStatement(sql) ;
			pstmt.setString(1,sps.getStepName()) ;
			pstmt.setString( 2, sps.getStepillustrate()) ;
			pstmt.setString(3, sps.getStepContext()) ;
			pstmt.setString(4, sps.getAttachment1name()) ;
			pstmt.setString( 5, sps.getAttachment1address()) ;
			pstmt.setString(6, sps.getAttachment2name()) ;
			pstmt.setString( 7, sps.getAttachment2address()) ;
			pstmt.setString(8, sps.getAttachment3name()) ;
			pstmt.setString( 9, sps.getAttachment3address()) ;
			pstmt.setString(10, sps.getAttachment4name()) ;
			pstmt.setString( 11, sps.getAttachment4address()) ;
			pstmt.setString(12, sps.getAttachment5name()) ;
			pstmt.setString( 13, sps.getAttachment5address()) ;
			pstmt.setInt(14,sps.getStepid()) ;
			pstmt.executeUpdate() ;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			temp = false ;
			e.printStackTrace();
		}finally{
			try {
				pstmt.close() ;
				cn.close() ;
				databaseAccess.close() ;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				temp = false ;
				e.printStackTrace();
			}
	}
		return temp;
	}

	public boolean addUsertoSubProject(int stepid ,int spid ,List<User> users) {
		// TODO Auto-generated method stub
		boolean  temp = true ;
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		cn = databaseAccess.getConn();
		for(int i=0;i<users.size();i++){
			User user  = users.get(i) ;
			String sql="insert into userdata(uid , stepid ,spid, stepstatus  , data ) values(?,?,?,?,?) ";
		try{
			pstmt = cn.prepareStatement(sql);
			pstmt.setInt(1, user.getUid());
			pstmt.setInt(2, stepid) ;
			pstmt.setInt(3, spid) ;
			pstmt.setInt(4, 0) ;
			pstmt.setString(5, user.toString()) ;
			pstmt.execute();
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			temp = false ;
		}
		}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					temp = false ;
					pstmt = null ;
					cn = null ;
					temp = false ;
			}
			return temp;
	}
	public Pager<Userdata> getUsersbyPro(String spid ,String name, String schoolId, String cellPhone, String email ,String type ,int pageNo) {
		
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs = null;
		List<Userdata> stuList = new ArrayList<Userdata>()  ;
		Pager<Userdata> page = new Pager<Userdata>() ;
		String hql = " AND  spid="+spid ;
		if(name !=null&&!"".equals(name)){
			hql += " AND data like '%" +name +"%' " ;
		}
		if(schoolId !=null&&!"".equals(schoolId)){
			hql += " AND data like '%"+ schoolId+"%' " ;
		}
		if(cellPhone !=null&&!"".equals(cellPhone)){
			hql += " AND data like '%"+ cellPhone+"%' " ;
		}
		if(email !=null&&!"".equals(email)){
			hql += " AND data like '%"+ email+"%' " ;
		}
		if(type !=null&&!"".equals(type)){
			if("0".equals(type))
			hql += " AND data like %\"studentorteacher\":0%  " ;
			else
			hql += " AND data like %\"studentorteacher\":1%  " ;
		}
		
		String sql = "select  id,uid ,stepid, stepstatus , data ,spid  from userdata  where 1=1 ";
		try{
			String fenyesql = " LIMIT " +Util.pageCount*(pageNo-1) + ","+Util.pageCount  ;
			System.out.println(sql + hql + fenyesql);
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql + hql + fenyesql);
			System.out.println(sql + hql + fenyesql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				
				Userdata ud= new Userdata();
				
				ud.setUid(rs.getInt("ID")) ;
				ud.setUid(rs.getInt("uid")) ;
				ud.setStepid(rs.getInt("stepid")) ;
				ud.setStepstatus(rs.getInt("stepstatus")) ;
				System.out.println(rs.getString("data"));
				ud.setUser(JsonUtils.parse(rs.getString("data"), User.class)) ;
				ud.setSpid(rs.getInt("spid")) ;
				stuList.add(ud) ;
				
			}
			int total = this.gettotalCount(hql  ,cn , rs , pstmt,"userdata" ,"id") ;
			page.setTotal(total) ;
			page.setTotalPage(total) ;
			page.setDatas(stuList) ;
			System.out.println(page.getDatas().size());
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
			}
		}
		return page ;
	}
	public List<Userdata> getUsersbyStepid(String spid ) {
		
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs = null;
		List<Userdata> userList = new ArrayList<Userdata>()  ;
		String hql = " AND  spid="+spid ;
		
		String sql = "select  id,uid ,stepid, stepstatus , data ,spid  from userdata  where 1=1 AND stepstatus = 0  ";
		try{
			System.out.println(sql + hql );
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql + hql );
			rs = pstmt.executeQuery();
			while(rs.next()){
				
				Userdata ud= new Userdata();
				
				ud.setUid(rs.getInt("ID")) ;
				ud.setUid(rs.getInt("uid")) ;
				ud.setStepid(rs.getInt("stepid")) ;
				ud.setStepstatus(rs.getInt("stepstatus")) ;
				System.out.println(rs.getString("data"));
				ud.setUser(JsonUtils.parse(rs.getString("data"), User.class)) ;
				ud.setSpid(rs.getInt("spid")) ;
				userList.add(ud) ;
				
			}
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
			}
		}
		return userList ;
	}
	public boolean delUserfromSub(int spid, int uid) {
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		boolean result = true;
		Connection cn = null;
		PreparedStatement pstmt=null;
		String hql = "delete from userdata   WHERE UID =? AND spid =? ";
		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(hql);
			pstmt.setInt(1, uid);
			pstmt.setInt(2, spid);
			pstmt.execute();
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			result = false ;
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close() ;
				databaseAccess.close() ;
			}catch(Exception ex){
				result = false ;
			}
		}
		return result;
	}

	
	public boolean toNextStep(int stepid ,int uid){
		boolean temp = true ;
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		String sql = "update  userdata  set  stepid =? , stepstatus =?  where uid= ? AND stepid =? " ;
		System.out.println(sql) ;
		try {
			cn = databaseAccess.getConn() ;
			pstmt = cn.prepareStatement(sql) ;
			pstmt.setInt(1,stepid+1) ;
			pstmt.setInt( 2, 0) ;
			pstmt.setInt(3, uid) ;
			pstmt.setInt(4, stepid) ;
		
			pstmt.executeUpdate() ;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			temp = false ;
			e.printStackTrace();
		}finally{
			try {
				pstmt.close() ;
				cn.close() ;
				databaseAccess.close() ;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}
		return temp;
	}
	
	public boolean updateUserdata(int stepid ,int uid,User user) {
		
		boolean temp = true ;
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		String sql = "update userdata  set data =?   where stepid = ? AND uid = ?" ;

		try {
			cn = databaseAccess.getConn() ;
			pstmt = cn.prepareStatement(sql) ;
			pstmt.setString(1,user.toString() ) ;
			pstmt.setInt(2,stepid) ;
			pstmt.setInt(3,uid) ;
			pstmt.executeUpdate() ;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			temp = false ;
			e.printStackTrace();
		}finally{
			try {
				pstmt.close() ;
				cn.close() ;
				databaseAccess.close() ;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				temp = false ;
				e.printStackTrace();
			}
	}
		return temp;
	}
	
	public int gettotalCount(String sql,Connection cn ,ResultSet rs ,PreparedStatement pstmt ,String tablename,String tablenameid) {
		// TODO Auto-generated method stub
		return gettotalCount(sql, tablename, tablenameid, cn , rs , pstmt );
	}


}
